
add jar hdfs:///StarRocks/jars/starrocks-spark2_2.11-1.0.0.jar;
CREATE TEMPORARY VIEW  dwm_yl_rt_ssmx_bill_detail_hi
USING starrocks
OPTIONS
(
    "starrocks.table.identifier" = "jms_dwm.dwm_yl_rt_ssmx_bill_detail",
    "starrocks.fenodes" = "pro-time-dorisdb-31.bigdata.yl.com:8030",
    "user" = "dbadmin",
    "password" = "sn4TqJT78ZoW",
    "starrocks.batch.size" = "10000",
    "starrocks.exec.mem.limit" = "61474836480",
    "starrocks.request.tablet.size" = "6",
    "starrocks.filter.query" = "gmt_update_date between '{{ execution_date | hour_add(-1)| cst_hour_ds }}:00:00' and '{{ execution_date | hour_add(1) | cst_hour_ds }}:00:00' or order_input_date between '{{ execution_date | hour_add(-1) | cst_hour_ds }}:00:00' and '{{ execution_date | hour_add(1) | cst_hour_ds }}:00:00' ",
    "starrocks.request.read.timeout.ms" = "300000"
);
insert overwrite table jms_ods.dwm_yl_rt_ssmx_bill_detail_hi partition (dt)
select
bill_code
,package_code
,transfer_code
,latest_next_station_code
,pick_network_code
,pick_date
,latest_scan_date
,latest_scan_type
,latest_scan_code
,latest_scan_user_code
,latest_scan_user_name
,customer_code
,customer_name
,liable_network_type
,liable_network_code
,difficult_info
,not_valid
,package_inside_charge_weight
,goods_name
,goods_type
,express_type_code
,sender_province_id
,sender_province_name
,sender_city_id
,sender_city_name
,area_type
,order_source_code
,input_date
,rec_date
,receiver_province_id
,receiver_province_name
,receiver_city_id
,receiver_city_name
,destination_agent_code
,destination_agent_name
,biz_sign_site_code
,biz_sign_date
,is_problem
,is_intercept
,is_reback
,is_damaged
,is_mislaid
,is_same_province
,is_same_city
,forecast_last_site_code
,forecast_last_site_name
,forecast_last_site_agent_name
,forecast_last_site_agent_code
,forecast_last_center_code
,forecast_last_center_name
,last_center_next_site_code
,last_center_next_site_name
,last_center_come_date
,last_center_scan_user
,last_center_scan_user_code
,planned_delivery_date
,shift
,problem_desc_code
,problem_desc
,pre_scan_code
,pre_scan_date
,pre_scan_type
,problem_scan_date
,problem_scan_site_name
,problem_scan_site_code
,order_input_date
,is_finish
,gmt_create_date
,gmt_update_date
-- ,pre_scan_name
-- ,pre_scan_site_agent_name
-- ,pre_scan_site_agent_code
-- ,pre_scan_site_franchisee_name
-- ,pre_scan_site_franchisee_code
,latest_scan_site_type
,version
,rec_site_code
,rec_user_code
,rec_user_name
,terminal_dispatch_code
,ware_house_site_code
,ware_house_send_deliver_user_code
,ware_house_pistol_id
,ware_house_site_date
,first_center_code
,first_center_send_date
,first_center_name
,first_center_come_date
,ware_next_site_code
,ware_next_site_name
,ware_next_send_date
,first_bagging_site_code
,first_bagging_site_name
,first_bagging_site_date
,last_bagging_site_code
,last_bagging_site_name
,last_bagging_site_date
,last_center_name
,last_center_code
,last_center_send_date
,deliver_date
,deliver_site_code
,deliver_site_name
,collect_in_site_date
,collect_in_site_name
,collect_in_site_code
,collect_out_site_date
,collect_out_site_name
,collect_out_site_code
,biz_sign_name
,sign_site_name
,sign_date
,sign_site_code
,pre_liable_network_type
,pre_liable_network_code
,end_date
,bill_state
,intercept_network_name
,intercept_network_code
,intercept_date
,is_in_house
,order_id
,order_source_name
,pre_scan_user_code
,pre_scan_user_name
,end_code_desc
,end_code
,last_site_code
,last_site_name
,last_site_come_date
,last_site_come_code
,last_site_come_name
,first_collect_in_site_code
,first_collect_in_site_name
,first_collect_in_site_come_date
,first_collect_in_site_send_date
,last_collect_in_site_code
,last_collect_in_site_name
,last_collect_in_site_come_date
,last_collect_in_site_send_date
,send_deliver_user
,send_deliver_user_id
,is_receive2
,problem_type_name
,second_level_type_code
,second_level_type_name
,registration_network_code
,registration_network_name
,receive_network_code
,receive_network_name
,problem_type_subject_code
,problem_type_subject_id
,problem_type_subject_name
,sender_network_id
,registration_time
,predict_come_site_date
,station_brand
,reback_transfer_network_name
,reback_transfer_network_code
,is_transfer
,third_code
,receiver_detail_address
,receiver_area_name
,first_transfer_center_name
,first_transfer_center_code
,first_transfer_center_send_date
,first_transfer_center_come_date
,second_transfer_center_send_date
,second_transfer_center_come_date
,second_transfer_center_name
,second_transfer_center_code
,third_transfer_center_send_date
,third_transfer_center_come_date
,third_transfer_center_name
,third_transfer_center_code
,latest_pre_scan_site_code
,latest_pre_scan_site_name
,pre_site_code
,pre_site_name
,pre_site_date
,first_site_loading_send_shipment_no
,first_site_loading_send_planned_departure_date
,first_site_loading_send_planned_arrival_date
,first_collect_in_site_loading_send_shipment_no
,first_collect_in_site_loading_planned_arrival_date
,first_collect_in_site_loading_planned_departure_date
,first_center_unloading_arrival_shipment_no
,first_center_unloading_arrival_planned_departure_date
,first_center_unloading_arrival_planned_arrival_date
,first_center_loading_arrival_shipment_no
,first_center_loading_arrival_planned_departure_date
,first_center_loading_arrival_planned_arrival_date
,last_center_unloading_arrival_shipment_no
,last_center_unloading_arrival_planned_departure_date
,last_center_unloading_arrival_planned_arrival_date
,last_center_loading_send_shipment_no
,last_center_loading_send_planned_departure_date
,last_center_loading_send_planned_arrival_date
-- 添加字段
,latest_next_station_name
,off_order_input_date
,mark
,way_bill_input_time
,is_arrival_scan
,is_distribute_scan
,order_rec_end_date
,pick_staff_code
,pick_staff_name
,dispatch_network_code
,dispatch_network_name
,waybill_status_code

,date_format(if(gmt_update_date like '1999%', order_input_date,gmt_update_date),'yyyy-MM-dd-HH') as dt
from dwm_yl_rt_ssmx_bill_detail_hi
distribute by dt,pmod(hash(rand()),50);